package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import jp.bsws.bean.MasterBean;
import jp.bsws.bean.RaitenJohoBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.TenpoRelationModel;
import jp.bsws.model.UriageModel;
import jp.bsws.model.UriageNikkeiModel;
import jp.bsws.model.UriageSyosaiModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class UriageDao {
	private static UriageDao uriageDao = new UriageDao();

	/** ログ */
	private Log log = LogFactory.getLog(UriageDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static UriageDao getInstance(){
		return uriageDao;
	}

	private String SQL_SELECT_URIAGE_NIKKEI_INFO =
		" SELECT " +
		"     UN.URIAGE_YMD " +
		"    ,UN.JUGYOIN_SEQ " +
		"    ,J.JUGYOIN_KNJ " +
		"    ,UN.SHIME_FLG " +
		" FROM " +
		"     T_URIAGE_NIKKEI UN, " +
		"     M_JUGYOIN J ";
	/**
	 * 売上日計情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public UriageNikkeiModel getUriageNikkeiRow(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd) throws Exception{
		//検索条件の作成
		String where = " WHERE " +
		"         UN.GROUP_COD = J.GROUP_COD " +
		"     AND UN.TENPO_COD = J.TENPO_COD " +
		"     AND UN.JUGYOIN_SEQ = J.SEQ ";

		//検索条件の作成
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " UN.GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " UN.TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		String where2 = "";
		List<Integer> where2List = new ArrayList<Integer>();
		if( pkUriageYmd != null ){
			where2 = BSWSUtils.getWhereAnd(where) + "  ? BETWEEN J.NYUTEN_YMD AND J.TAITEN_YMD ";
			where2List.add(pkUriageYmd);
		}
		if( pkUriageYmd != null ){
			where2 += BSWSUtils.getWhereAnd(where) + " UN.URIAGE_YMD = ? ";
			where2List.add(pkUriageYmd);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_NIKKEI_INFO + where + where2);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			seqCnt = i + 1;
			stmt.setString(i + 1, (String)whereList.get(i));
		}
		for(int k = 0; k < where2List.size(); k++){
			stmt.setInt(seqCnt + k + 1, (Integer)where2List.get(k));
		}

		ResultSet rs = stmt.executeQuery();

		UriageNikkeiModel uriageNikkeiModel = null;
		while(rs.next()){
			uriageNikkeiModel = new UriageNikkeiModel();
			uriageNikkeiModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageNikkeiModel.setIntJugyoinSeq(rs.getInt("JUGYOIN_SEQ"));
			uriageNikkeiModel.setStrJugyoinKnj(rs.getString("JUGYOIN_KNJ"));
			uriageNikkeiModel.setStrShimeFlg(rs.getString("SHIME_FLG"));
		}

		rs.close();
		stmt.close();

		return uriageNikkeiModel;
	}


	private String SQL_DELETE_URIAGE_NIKKEI =
		" DELETE FROM " +
		"     T_URIAGE_NIKKEI ";

	/**
	 * 売上情報削除
	 * 引数のPKがあれば条件とする。
	 */
	public int deleteUriageNikkeiInfo(Connection con, BSWSSession bSWSSession, String pkTenpoCod, Integer pkUriageYmd) throws Exception{

		//削除条件の作成
		String where = " WHERE URIAGE_YMD = ? ";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(bSWSSession.getStrGroupCod())){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(bSWSSession.getStrGroupCod());
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_DELETE_URIAGE_NIKKEI + where);

		stmt.setInt(1, pkUriageYmd);
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 2, (String)whereList.get(i));
		}

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_INSERT_URIAGE_NIKKEI_INFO =
		" INSERT INTO " +
		"     T_URIAGE_NIKKEI ( " +
		"         GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,URIAGE_YMD " +
		"        ,JUGYOIN_SEQ " +
		"        ,SHIME_FLG " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 売上日計情報追加
	 */
	public int insertUriageNikkeiInfo(Connection con,
			BSWSSession bSWSSession , String strSansyoTenpoCod, Integer pkUriageYmd, String strShimeFlg) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_INSERT_URIAGE_NIKKEI_INFO);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setString(2, strSansyoTenpoCod);
		stmt.setInt(3, pkUriageYmd);
		stmt.setString(4, bSWSSession.getStrJugyoinCod());
		stmt.setString(5, strShimeFlg);
		stmt.setString(6, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_SELECT_URIAGE =
		" SELECT " +
		"     J.GROUP_COD " +
		"    ,J.TENPO_COD " +
		"    ,U.URIAGE_YMD " +
		"    ,J.SEQ " +
		"    ,J.JUGYOIN_KNJ " +
		"    ,U.SHINKI_SU " +
		"    ,U.SAIRAI_SU " +
		"    ,U.SHIMEI_SU " +
		"    ,U.NYUKYAKU_SU " +
		"    ,U.HONTAI_KIN " +
		"    ,U.SYOHIZEI_KIN " +
		"    ,U.URIAGE_GOKEI_KIN " +
		"    ,U.TANKA_KIN " +
		"    ,U.HOSYU_GOKEI_KIN " +
		" FROM " +
		"     T_URIAGE U " +
		"     LEFT OUTER JOIN M_JUGYOIN J ON ( " +
		"         U.GROUP_COD = J.GROUP_COD " +
		"     AND U.TENPO_COD = J.TENPO_COD " +
		"     AND U.JUGYOIN_SEQ = J.SEQ " +
		"     AND U.URIAGE_YMD = ? " +
		"     ) ";

	/**
	 * 売上情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageModel> getUriageJoho(Connection con, String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd) throws Exception{

		List<UriageModel> resultList = new ArrayList<UriageModel>();

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " J.GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " J.TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		String whereSeq = "";
		if( pkUriageYmd != null ){
			whereSeq = BSWSUtils.getWhereAnd(where) + "  ? BETWEEN J.NYUTEN_YMD AND J.TAITEN_YMD ";
		}

		String orderBy = " ORDER BY J.GROUP_COD,J.TENPO_COD,J.SEQ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE + where + whereSeq + orderBy);

		stmt.setInt(1, pkUriageYmd);
		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			seqCnt = i + 2;
			stmt.setString(seqCnt, (String)whereList.get(i));
		}
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			stmt.setInt(seqCnt + 1, pkUriageYmd);
		}

		ResultSet rs = stmt.executeQuery();

		UriageModel uriageModel = null;
		while(rs.next()){
			uriageModel = new UriageModel();
			uriageModel.setStrGroupCod(rs.getString("GROUP_COD"));
			uriageModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			uriageModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageModel.setIntJugyoinSeq(rs.getInt("SEQ"));
			uriageModel.setStrJugyoinKnj(rs.getString("JUGYOIN_KNJ"));
			uriageModel.setIntShinkiSu(rs.getInt("SHINKI_SU"));
			uriageModel.setIntSairaiSu(rs.getInt("SAIRAI_SU"));
			uriageModel.setIntShimeiSu(rs.getInt("SHIMEI_SU"));
			uriageModel.setIntNyukyakuSu(rs.getInt("NYUKYAKU_SU"));
			uriageModel.setIntHontaiKin(rs.getInt("HONTAI_KIN"));
			uriageModel.setIntSyohizeiKin(rs.getInt("SYOHIZEI_KIN"));
			uriageModel.setIntUriageGokeiKin(rs.getInt("URIAGE_GOKEI_KIN"));
			uriageModel.setIntTankaKin(rs.getInt("TANKA_KIN"));
			uriageModel.setIntHosyuGokeiKin(rs.getInt("HOSYU_GOKEI_KIN"));
			resultList.add(uriageModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_SELECT_JUGYOIN =
		" SELECT " +
		"   TR.GROUP_COD " +
		"  ,TR.TENPO_COD " +
		"  ,? URIAGE_YMD " +
		"  ,TR.SEQ " +
		"  ,J.JUGYOIN_KNJ " +
		"  ,0 SHINKI_SU " +
		"  ,0 SAIRAI_SU " +
		"  ,0 SHIMEI_SU " +
		"  ,0 NYUKYAKU_SU " +
		"  ,0 HONTAI_KIN " +
		"  ,0 SYOHIZEI_KIN " +
		"  ,0 URIAGE_GOKEI_KIN " +
		"  ,0 TANKA_KIN " +
		"  ,0 HOSYU_GOKEI_KIN " +
		" FROM T_TENPO_RELATION TR " +
		" INNER JOIN  M_JUGYOIN J   ON ( " +
		"   TR.GROUP_COD = J.GROUP_COD     " +
		"   AND TR.TENPO_COD = J.TENPO_COD    " +
		"   AND TR.SEQ = J.SEQ " +
		"   AND TR.RELATION_KBN = 7 " +
		"   AND TR.GROUP_COD = ? " +
		"   AND TR.TENPO_COD = ? " +
		"   AND ? BETWEEN J.NYUTEN_YMD AND J.TAITEN_YMD  " +
		" ) " +
		" ORDER BY TR.GROUP_COD,TR.TENPO_COD,TR.SEQ ";

	/**
	 * 売上情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageModel> getUriageJugyoin(Connection con, String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd) throws Exception{

		List<UriageModel> resultList = new ArrayList<UriageModel>();

		//検索条件の作成
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_JUGYOIN);

		stmt.setInt(1, pkUriageYmd);
		stmt.setString(2, pkGroupCod);
		stmt.setString(3, pkTenpoCod);
		stmt.setInt(4, pkUriageYmd);

		ResultSet rs = stmt.executeQuery();

		UriageModel uriageModel = null;
		while(rs.next()){
			uriageModel = new UriageModel();
			uriageModel.setStrGroupCod(rs.getString("GROUP_COD"));
			uriageModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			uriageModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageModel.setIntJugyoinSeq(rs.getInt("SEQ"));
			uriageModel.setStrJugyoinKnj(rs.getString("JUGYOIN_KNJ"));
			uriageModel.setIntShinkiSu(rs.getInt("SHINKI_SU"));
			uriageModel.setIntSairaiSu(rs.getInt("SAIRAI_SU"));
			uriageModel.setIntShimeiSu(rs.getInt("SHIMEI_SU"));
			uriageModel.setIntNyukyakuSu(rs.getInt("NYUKYAKU_SU"));
			uriageModel.setIntHontaiKin(rs.getInt("HONTAI_KIN"));
			uriageModel.setIntSyohizeiKin(rs.getInt("SYOHIZEI_KIN"));
			uriageModel.setIntUriageGokeiKin(rs.getInt("URIAGE_GOKEI_KIN"));
			uriageModel.setIntTankaKin(rs.getInt("TANKA_KIN"));
			uriageModel.setIntHosyuGokeiKin(rs.getInt("HOSYU_GOKEI_KIN"));
			resultList.add(uriageModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_DELETE_URIAGE =
		" DELETE FROM " +
		"     T_URIAGE ";

	/**
	 * 売上情報削除
	 * 引数のPKがあれば条件とする。
	 */
	public int deleteUriageJoho(Connection con, String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd) throws Exception{

		//削除条件の作成
		String where = " WHERE URIAGE_YMD = ? ";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_DELETE_URIAGE + where);

		stmt.setInt(1, pkUriageYmd);
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 2, (String)whereList.get(i));
		}

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_INSERT_URIAGE =
		" INSERT INTO " +
		"     T_URIAGE ( " +
		"         GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,URIAGE_YMD " +
		"        ,JUGYOIN_SEQ " +
		"        ,SHINKI_SU " +
		"        ,SAIRAI_SU " +
		"        ,SHIMEI_SU " +
		"        ,NYUKYAKU_SU " +
		"        ,HONTAI_KIN " +
		"        ,SYOHIZEI_KIN " +
		"        ,URIAGE_GOKEI_KIN " +
		"        ,TANKA_KIN " +
		"        ,HOSYU_GOKEI_KIN " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 売上情報追加
	 */
	public int insertUriage(Connection con,
			BSWSSession bSWSSession , String strSansyoTenpoCod, Integer pkUriageYmd, RaitenJohoBean raitenJohoBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_INSERT_URIAGE);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setString(2, strSansyoTenpoCod);
		stmt.setInt(3, pkUriageYmd);
		stmt.setInt(4, raitenJohoBean.getIntTantoSeq());
		stmt.setInt(5, raitenJohoBean.getIntShinki());
		stmt.setInt(6, raitenJohoBean.getIntSairai());
		stmt.setInt(7, raitenJohoBean.getIntShimei());
		stmt.setInt(8, raitenJohoBean.getIntNyukyaku());
		stmt.setInt(9, raitenJohoBean.getIntHontaiKingaku());
		stmt.setInt(10, raitenJohoBean.getIntSyohizei());
		stmt.setInt(11, raitenJohoBean.getIntUriageGokei());
		stmt.setInt(12, raitenJohoBean.getIntTanka());
		stmt.setInt(13, raitenJohoBean.getIntHosyuGokei());
		stmt.setString(14, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}











	private String SQL_SELECT_URIAGE_SYOSAI =
		" SELECT " +
		"     GROUP_COD " +
		"    ,TENPO_COD " +
		"    ,URIAGE_YMD " +
		"    ,JUGYOIN_SEQ " +
		"    ,KBN " +
		"    ,SEQ " +
		"    ,ATAI " +
		" FROM " +
		"     T_URIAGE_SYOSAI ";

	/**
	 * 売上詳細情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageSyosaiModel> getUriageSyosaiJoho(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd, Integer pkJugyoinSeq, Integer pkKbn, Integer pkSeq) throws Exception{

		List<UriageSyosaiModel> resultList = new ArrayList<UriageSyosaiModel>();

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		ArrayList<Integer> whereIntList = new ArrayList<Integer>();
		if( pkUriageYmd != null ){
			where += BSWSUtils.getWhereAnd(where) + " URIAGE_YMD = ? ";
			whereIntList.add(pkUriageYmd);
		}
		if( pkJugyoinSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			where += BSWSUtils.getWhereAnd(where) + " KBN = ? ";
			whereIntList.add(pkKbn);
		}
		if( pkSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " SEQ = ? ";
			whereIntList.add(pkSeq);
		}

		String orderBy = " ORDER BY  GROUP_COD, TENPO_COD, URIAGE_YMD, JUGYOIN_SEQ, KBN, SEQ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_SYOSAI + where + orderBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		UriageSyosaiModel uriageSyosaiModel = null;
		while(rs.next()){
			uriageSyosaiModel = new UriageSyosaiModel();
			uriageSyosaiModel.setStrGroupCod(rs.getString("GROUP_COD"));
			uriageSyosaiModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			uriageSyosaiModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageSyosaiModel.setIntJugyoinSeq(rs.getInt("JUGYOIN_SEQ"));
			uriageSyosaiModel.setIntKbn(rs.getInt("KBN"));
			uriageSyosaiModel.setIntSeq(rs.getInt("SEQ"));
			uriageSyosaiModel.setIntAtai(rs.getInt("ATAI"));
			resultList.add(uriageSyosaiModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_DELETE_SYOSAI =
		" DELETE FROM " +
		"     T_URIAGE_SYOSAI ";

	/**
	 * 売上来店要因情報削除
	 * 引数のPKがあれば条件とする。
	 */
	public int deleteUriageSyosai(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd, Integer pkJugyoinSeq, Integer pkKbn, Integer pkSeq) throws Exception{

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		ArrayList<Integer> whereIntList = new ArrayList<Integer>();
		if( pkUriageYmd != null ){
			where += BSWSUtils.getWhereAnd(where) + " URIAGE_YMD = ? ";
			whereIntList.add(pkUriageYmd);
		}
		if( pkJugyoinSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			where += BSWSUtils.getWhereAnd(where) + " KBN = ? ";
			whereIntList.add(pkKbn);
		}
		if( pkSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " SEQ = ? ";
			whereIntList.add(pkSeq);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_DELETE_SYOSAI + where);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
		}
		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_INSERT_URIAGE_SYOSAI =
		" INSERT INTO " +
		"     T_URIAGE_SYOSAI ( " +
		"         GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,URIAGE_YMD " +
		"        ,JUGYOIN_SEQ " +
		"        ,KBN " +
		"        ,SEQ " +
		"        ,ATAI " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 売上来店要因情報追加
	 */
	public int insertUriageSyosai(Connection con,
			BSWSSession bSWSSession ,
			String strSansyoTenpoCod,
			String strSansyoYmd,
			Integer intJugyoinSeq,
			Integer intKbn,
			Integer intSeq,
			Integer intAtai) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_INSERT_URIAGE_SYOSAI);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setString(2, strSansyoTenpoCod);
		stmt.setInt(3, Integer.parseInt(strSansyoYmd));
		stmt.setInt(4, intJugyoinSeq);
		stmt.setInt(5, intKbn);
		stmt.setInt(6, intSeq);
		stmt.setInt(7, intAtai);
		stmt.setString(8, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_SELECT_URIAGE_GEKKEI =
		" SELECT " +
		"     H.YMD " +
		"    ,SUM(U.SHINKI_SU)  SHINKI_SU" +
		"    ,SUM(U.SAIRAI_SU)  SAIRAI_SU" +
		"    ,SUM(U.SHIMEI_SU)  SHIMEI_SU" +
		"    ,SUM(U.NYUKYAKU_SU)  NYUKYAKU_SU" +
		"    ,SUM(U.HONTAI_KIN)  HONTAI_KIN" +
		"    ,SUM(U.SYOHIZEI_KIN)  SYOHIZEI_KIN" +
		"    ,SUM(U.URIAGE_GOKEI_KIN)  URIAGE_GOKEI_KIN" +
		"    ,SUM(U.TANKA_KIN)  TANKA_KIN" +
		"    ,SUM(U.HOSYU_GOKEI_KIN)  HOSYU_GOKEI_KIN" +
		" FROM " +
		"     M_HIDUKE H " +
		"     LEFT OUTER JOIN T_URIAGE U ON ( ";

	/**
	 * 売上情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageModel> getUriageGekkeiJoho(Connection con
			, String pkGroupCod, String pkTenpoCod, Integer pkJugyoinSeq
			, Integer pkUriageYmdFrom, Integer pkUriageYmdTo) throws Exception{

		List<UriageModel> resultList = new ArrayList<UriageModel>();

		//検索条件の作成
		String where = " H.YMD = U.URIAGE_YMD ";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " U.GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " U.TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " U.JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}

		String where2 = "";
		List<Integer> where2List = new ArrayList<Integer>();
		if( pkUriageYmdFrom != null && pkUriageYmdTo != null){
			where2 = " WHERE  H.YMD BETWEEN ? AND ? ";
			where2List.add(pkUriageYmdFrom);
			where2List.add(pkUriageYmdTo);
		}
		String tojiKakko = " ) ";
		String groupBy = " GROUP BY H.YMD ";
		String orderBy = " ORDER BY H.YMD ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_GEKKEI + where + tojiKakko + where2 + groupBy + orderBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
		}

		for(int i = 0; i < where2List.size(); i++){
			stmt.setInt(seqCnt + i + 1, (Integer)where2List.get(i));
		}

		ResultSet rs = stmt.executeQuery();

		UriageModel uriageModel = null;
		while(rs.next()){
			uriageModel = new UriageModel();
			uriageModel.setIntUriageYmd(rs.getInt("YMD"));
			uriageModel.setIntShinkiSu(rs.getInt("SHINKI_SU"));
			uriageModel.setIntSairaiSu(rs.getInt("SAIRAI_SU"));
			uriageModel.setIntShimeiSu(rs.getInt("SHIMEI_SU"));
			uriageModel.setIntNyukyakuSu(rs.getInt("NYUKYAKU_SU"));
			uriageModel.setIntHontaiKin(rs.getInt("HONTAI_KIN"));
			uriageModel.setIntSyohizeiKin(rs.getInt("SYOHIZEI_KIN"));
			uriageModel.setIntUriageGokeiKin(rs.getInt("URIAGE_GOKEI_KIN"));
			uriageModel.setIntTankaKin(rs.getInt("TANKA_KIN"));
			uriageModel.setIntHosyuGokeiKin(rs.getInt("HOSYU_GOKEI_KIN"));
			resultList.add(uriageModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_SELECT_URIAGE_SYOSAI_GEKKEI =
		" SELECT " +
		"     GROUP_COD " +
		"    ,TENPO_COD " +
		"    ,URIAGE_YMD " +
		"    ,KBN " +
		"    ,SEQ " +
		"    ,SUM(ATAI) ATAI " +
		" FROM " +
		"     T_URIAGE_SYOSAI ";

	/**
	 * 売上詳細情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageSyosaiModel> getUriageGekkeiSyosaiJoho(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkJugyoinSeq,
			Integer pkKbn, Integer pkSeq,
			Integer pkUriageYmdFrom, Integer pkUriageYmdTo) throws Exception{

		List<UriageSyosaiModel> resultList = new ArrayList<UriageSyosaiModel>();

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		ArrayList<Integer> whereIntList = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			where += BSWSUtils.getWhereAnd(where) + " KBN = ? ";
			whereIntList.add(pkKbn);
		}
		if( pkSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " SEQ = ? ";
			whereIntList.add(pkSeq);
		}
		ArrayList<Integer> where2List = new ArrayList<Integer>();
		if( pkUriageYmdFrom != null && pkUriageYmdTo != null){
			where += BSWSUtils.getWhereAnd(where) +  " URIAGE_YMD  BETWEEN ? AND ? ";
			where2List.add(pkUriageYmdFrom);
			where2List.add(pkUriageYmdTo);
		}
		String groupBy = " GROUP BY URIAGE_YMD, KBN, SEQ ";
		String orderBy = " ORDER BY URIAGE_YMD, KBN, SEQ ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_SYOSAI_GEKKEI + where + groupBy + orderBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < where2List.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)where2List.get(i));
		}

		ResultSet rs = stmt.executeQuery();

		UriageSyosaiModel uriageSyosaiModel = null;
		while(rs.next()){
			uriageSyosaiModel = new UriageSyosaiModel();
			uriageSyosaiModel.setStrGroupCod(rs.getString("GROUP_COD"));
			uriageSyosaiModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			uriageSyosaiModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageSyosaiModel.setIntKbn(rs.getInt("KBN"));
			uriageSyosaiModel.setIntSeq(rs.getInt("SEQ"));
			uriageSyosaiModel.setIntAtai(rs.getInt("ATAI"));
			resultList.add(uriageSyosaiModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	/**
	 * 売上情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageModel> getUriageNenkanJoho(Connection con
			, String pkGroupCod, String pkTenpoCod, Integer pkJugyoinSeq
			, Integer pkUriageYmdFrom, Integer pkUriageYmdTo) throws Exception{

		List<UriageModel> resultList = new ArrayList<UriageModel>();

		String tmpY =String.valueOf(pkUriageYmdFrom).substring(0,4);
		String tmpMDFrom =String.valueOf(pkUriageYmdFrom).substring(4,8);
		String tmpMDTo =String.valueOf(pkUriageYmdTo).substring(4,8);

		int iYYZen = Integer.parseInt(tmpY) - 1;
		String strYmdFrom = iYYZen + tmpMDFrom;
		String strYmdTo = iYYZen + tmpMDTo;
		Integer iYmdFrom = Integer.parseInt(strYmdFrom);
		Integer iYmdTo = Integer.parseInt(strYmdTo);

		String tmpYm =String.valueOf(pkUriageYmdFrom).substring(0,6);

		String SQL_SELECT_URIAGE_NENKAN_S =
			" SELECT " +
			"  U.URIAGE_YM " +
			" ,U.SHINKI_SU " +
			" ,U.SAIRAI_SU " +
			" ,U.SHIMEI_SU " +
			" ,U.NYUKYAKU_SU " +
			" ,U.HONTAI_KIN " +
			" ,U.SYOHIZEI_KIN " +
			" ,U.URIAGE_GOKEI_KIN " +
			" ,U.TANKA_KIN " +
			" ,U.HOSYU_GOKEI_KIN " +
			" ,UZ.SHINKI_SU_ZEN " +
			" ,UZ.SAIRAI_SU_ZEN " +
			" ,UZ.SHIMEI_SU_ZEN " +
			" ,UZ.NYUKYAKU_SU_ZEN " +
			" ,UZ.HONTAI_KIN_ZEN " +
			" ,UZ.SYOHIZEI_KIN_ZEN " +
			" ,UZ.URIAGE_GOKEI_KIN_ZEN " +
			" ,UZ.TANKA_KIN_ZEN " +
			" ,UZ.HOSYU_GOKEI_KIN_ZEN " +
			" FROM ";

		String SQL_SELECT_URIAGE_NENKAN_U =
			" ( " +
			" SELECT  " +
			tmpYm + " URIAGE_YM " +
			"    ,GROUP_COD " +
			"    ,TENPO_COD " +
			"    ,JUGYOIN_SEQ " +
			"    ,SUM(SHINKI_SU)  SHINKI_SU " +
			"    ,SUM(SAIRAI_SU)  SAIRAI_SU " +
			"    ,SUM(SHIMEI_SU)  SHIMEI_SU " +
			"    ,SUM(NYUKYAKU_SU)  NYUKYAKU_SU " +
			"    ,SUM(HONTAI_KIN)  HONTAI_KIN " +
			"    ,SUM(SYOHIZEI_KIN)  SYOHIZEI_KIN " +
			"    ,SUM(URIAGE_GOKEI_KIN)  URIAGE_GOKEI_KIN " +
			"    ,SUM(TANKA_KIN)  TANKA_KIN " +
			"    ,SUM(HOSYU_GOKEI_KIN)  HOSYU_GOKEI_KIN " +
			" FROM  " +
			"     T_URIAGE " +
			" WHERE  " +
			"     GROUP_COD = ? ";

		String SQL_SELECT_URIAGE_NENKAN_U_END =
			" ) U ";

		String SQL_SELECT_URIAGE_NENKAN_UZ =
			" LEFT OUTER JOIN ( " +
			" SELECT  " +
			tmpYm + " URIAGE_YM " +
			"    ,GROUP_COD " +
			"    ,TENPO_COD " +
			"    ,JUGYOIN_SEQ " +
			"    ,SUM(SHINKI_SU)  SHINKI_SU_ZEN " +
			"    ,SUM(SAIRAI_SU)  SAIRAI_SU_ZEN " +
			"    ,SUM(SHIMEI_SU)  SHIMEI_SU_ZEN " +
			"    ,SUM(NYUKYAKU_SU)  NYUKYAKU_SU_ZEN " +
			"    ,SUM(HONTAI_KIN)  HONTAI_KIN_ZEN " +
			"    ,SUM(SYOHIZEI_KIN)  SYOHIZEI_KIN_ZEN " +
			"    ,SUM(URIAGE_GOKEI_KIN)  URIAGE_GOKEI_KIN_ZEN " +
			"    ,SUM(TANKA_KIN)  TANKA_KIN_ZEN " +
			"    ,SUM(HOSYU_GOKEI_KIN)  HOSYU_GOKEI_KIN_ZEN " +
			" FROM  " +
			"     T_URIAGE " +
			" WHERE  " +
			"     GROUP_COD = ? ";

		String SQL_SELECT_URIAGE_NENKAN_UZ_END =
			" ) UZ ";

		String SQL_SELECT_URIAGE_NENKAN_W =
			" ON ( " +
			"     U.GROUP_COD = UZ.GROUP_COD " +
			" AND U.TENPO_COD = UZ.TENPO_COD " +
			" AND U.JUGYOIN_SEQ = UZ.JUGYOIN_SEQ " +
			" AND U.URIAGE_YM = UZ.URIAGE_YM )";

		//検索条件の作成
		String where = "";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			where += " AND JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkUriageYmdFrom != null && pkUriageYmdTo != null){
			where += " AND URIAGE_YMD BETWEEN ? AND ? ";
			whereIntList.add(pkUriageYmdFrom);
			whereIntList.add(pkUriageYmdTo);
		}

		String whereZen = "";
		List<String> whereListZen = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			whereZen += " AND TENPO_COD = ? ";
			whereListZen.add(pkTenpoCod);
		}
		List<Integer> whereIntListZen = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			whereZen += " AND JUGYOIN_SEQ = ? ";
			whereIntListZen.add(pkJugyoinSeq);
		}
		if( iYmdFrom != null && iYmdTo != null){
			whereZen += " AND URIAGE_YMD BETWEEN ? AND ? ";
			whereIntListZen.add(iYmdFrom);
			whereIntListZen.add(iYmdTo);
		}
		String groupBy = " GROUP BY URIAGE_YM ";
		String orderBy = " ORDER BY URIAGE_YM ";

		PreparedStatement stmt = con.prepareStatement(
				SQL_SELECT_URIAGE_NENKAN_S
				+ SQL_SELECT_URIAGE_NENKAN_U
				+ where
				+ SQL_SELECT_URIAGE_NENKAN_U_END
				+ SQL_SELECT_URIAGE_NENKAN_UZ
				+ whereZen
				+ SQL_SELECT_URIAGE_NENKAN_UZ_END
				+ SQL_SELECT_URIAGE_NENKAN_W
				+ groupBy
				+ orderBy);

		stmt.setString(1, pkGroupCod);
		int seqCnt = 1;
		int tmpCnt = seqCnt;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1 + tmpCnt, (String)whereList.get(i));
			seqCnt++;
		}
		tmpCnt = seqCnt;
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + 1 + tmpCnt, (Integer)whereIntList.get(i));
			seqCnt++;
		}
		stmt.setString(1 + seqCnt, pkGroupCod);
		seqCnt = seqCnt + 1;
		tmpCnt = seqCnt;
		for(int i = 0; i < whereListZen.size(); i++){
			stmt.setString(i + 1 + tmpCnt, (String)whereListZen.get(i));
			seqCnt++;
		}
		tmpCnt = seqCnt;
		for(int i = 0; i < whereIntListZen.size(); i++){
			stmt.setInt(i + 1 + tmpCnt, (Integer)whereIntListZen.get(i));
			seqCnt++;
		}

		ResultSet rs = stmt.executeQuery();

		UriageModel uriageModel = null;
		while(rs.next()){
			uriageModel = new UriageModel();
			uriageModel.setIntUriageYm(rs.getInt("URIAGE_YM"));
			//実績
			uriageModel.setIntShinkiSu(rs.getInt("SHINKI_SU"));
			uriageModel.setIntSairaiSu(rs.getInt("SAIRAI_SU"));
			uriageModel.setIntShimeiSu(rs.getInt("SHIMEI_SU"));
			uriageModel.setIntNyukyakuSu(rs.getInt("NYUKYAKU_SU"));
			uriageModel.setIntHontaiKin(rs.getInt("HONTAI_KIN"));
			uriageModel.setIntSyohizeiKin(rs.getInt("SYOHIZEI_KIN"));
			uriageModel.setIntUriageGokeiKin(rs.getInt("URIAGE_GOKEI_KIN"));
			uriageModel.setIntTankaKin(rs.getInt("TANKA_KIN"));
			uriageModel.setIntHosyuGokeiKin(rs.getInt("HOSYU_GOKEI_KIN"));
			//前年同月
			uriageModel.setIntShinkiSuZen(rs.getInt("SHINKI_SU_ZEN"));
			uriageModel.setIntSairaiSuZen(rs.getInt("SAIRAI_SU_ZEN"));
			uriageModel.setIntShimeiSuZen(rs.getInt("SHIMEI_SU_ZEN"));
			uriageModel.setIntNyukyakuSuZen(rs.getInt("NYUKYAKU_SU_ZEN"));
			uriageModel.setIntHontaiKinZen(rs.getInt("HONTAI_KIN_ZEN"));
			uriageModel.setIntSyohizeiKinZen(rs.getInt("SYOHIZEI_KIN_ZEN"));
			uriageModel.setIntUriageGokeiKinZen(rs.getInt("URIAGE_GOKEI_KIN_ZEN"));
			uriageModel.setIntTankaKinZen(rs.getInt("TANKA_KIN_ZEN"));
			uriageModel.setIntHosyuGokeiKinZen(rs.getInt("HOSYU_GOKEI_KIN_ZEN"));
			resultList.add(uriageModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	/**
	 * 売上詳細情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageSyosaiModel> getUriageNenkanSyosaiJoho(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkJugyoinSeq,
			Integer pkKbn, Integer pkSeq,
			Integer pkUriageYmdFrom, Integer pkUriageYmdTo) throws Exception{

		List<UriageSyosaiModel> resultList = new ArrayList<UriageSyosaiModel>();

		String tmpY =String.valueOf(pkUriageYmdFrom).substring(0,4);
		String tmpMDFrom =String.valueOf(pkUriageYmdFrom).substring(4,8);
		String tmpMDTo =String.valueOf(pkUriageYmdTo).substring(4,8);

		int iYYZen = Integer.parseInt(tmpY) - 1;
		String strYmdFrom = iYYZen + tmpMDFrom;
		String strYmdTo = iYYZen + tmpMDTo;
		Integer iYmdFromZen = Integer.parseInt(strYmdFrom);
		Integer iYmdToZen = Integer.parseInt(strYmdTo);

		String tmpYm =String.valueOf(pkUriageYmdFrom).substring(0,6);
		String selectTenpno = "";
		String whereTenpno = "";
		String groupByTenpno = "";
		String selectJugyoin = "";
		String whereJugyoin = "";
		String groupByJugyoin = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			selectTenpno = " ,TENPO_COD ";
			whereTenpno = " AND U.TENPO_COD = UZ.TENPO_COD";
			groupByTenpno = " ,TENPO_COD";
		}
		if( pkJugyoinSeq != null){
			selectJugyoin = " ,JUGYOIN_SEQ";
			whereJugyoin = " AND U.JUGYOIN_SEQ = UZ.JUGYOIN_SEQ";
			groupByJugyoin = " ,JUGYOIN_SEQ";
		}

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_S =
			" SELECT " +
			"  U.URIAGE_YM " +
			" ,U.KBN " +
			" ,U.SEQ " +
			" ,U.ATAI " +
			" ,UZ.ATAI_ZEN " +
			" FROM ";

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_U =
			" ( " +
			" SELECT  " +
			tmpYm + " URIAGE_YM " +
			"    ,GROUP_COD " +
			selectTenpno +
			selectJugyoin +
			"    ,KBN " +
			"    ,SEQ " +
			"    ,SUM(ATAI)  ATAI " +
			" FROM  " +
			"     T_URIAGE_SYOSAI " +
			" WHERE  " +
			"     GROUP_COD = ? ";

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_U_END =
			" GROUP BY  " +
			"  URIAGE_YM  " +
			" ,GROUP_COD  " +
			groupByTenpno +
			groupByJugyoin +
			" ,KBN  " +
			" ,SEQ  " +
			" ) U ";

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_UZ =
			" LEFT OUTER JOIN ( " +
			" SELECT  " +
			tmpYm + " URIAGE_YM " +
			"    ,GROUP_COD " +
			selectTenpno +
			selectJugyoin +
			"    ,KBN " +
			"    ,SEQ " +
			"    ,SUM(ATAI)  ATAI_ZEN " +
			" FROM  " +
			"     T_URIAGE_SYOSAI " +
			" WHERE  " +
			"     GROUP_COD = ? ";

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_UZ_END =
			" GROUP BY  " +
			"  URIAGE_YM  " +
			" ,GROUP_COD  " +
			groupByTenpno +
			groupByJugyoin +
			" ,KBN  " +
			" ,SEQ  " +
			" ) UZ ";

		String SQL_SELECT_URIAGE_SYOSAI_NENKAN_W =
			" ON ( " +
			"     U.GROUP_COD = UZ.GROUP_COD " +
			whereTenpno +
			whereJugyoin +
			" AND U.URIAGE_YM = UZ.URIAGE_YM " +
			" AND U.KBN = UZ.KBN " +
			" AND U.SEQ = UZ.SEQ ) ";


		//検索条件の作成
		String where = "";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			where += " AND JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			where += " AND KBN = ? ";
			whereIntList.add(pkKbn);
		}
		if( pkSeq != null){
			where += " AND SEQ = ? ";
			whereIntList.add(pkSeq);
		}
		if( pkUriageYmdFrom != null && pkUriageYmdTo != null){
			where += " AND URIAGE_YMD BETWEEN ? AND ? ";
			whereIntList.add(pkUriageYmdFrom);
			whereIntList.add(pkUriageYmdTo);
		}

		String whereZen = "";
		List<String> whereListZen = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			whereZen += " AND TENPO_COD = ? ";
			whereListZen.add(pkTenpoCod);
		}
		List<Integer> whereIntListZen = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			whereZen += " AND JUGYOIN_SEQ = ? ";
			whereIntListZen.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			whereZen += " AND KBN = ? ";
			whereIntListZen.add(pkKbn);
		}
		if( pkSeq != null){
			whereZen += " AND SEQ = ? ";
			whereIntListZen.add(pkSeq);
		}
		if( iYmdFromZen != null && iYmdToZen != null){
			whereZen += " AND URIAGE_YMD BETWEEN ? AND ? ";
			whereIntListZen.add(iYmdFromZen);
			whereIntListZen.add(iYmdToZen);
		}
		String groupBy = " GROUP BY URIAGE_YM, KBN, SEQ ";
		String orderBy = " ORDER BY URIAGE_YM, KBN, SEQ ";

		PreparedStatement stmt = con.prepareStatement(
				SQL_SELECT_URIAGE_SYOSAI_NENKAN_S
				+ SQL_SELECT_URIAGE_SYOSAI_NENKAN_U
				+ where
				+ SQL_SELECT_URIAGE_SYOSAI_NENKAN_U_END
				+ SQL_SELECT_URIAGE_SYOSAI_NENKAN_UZ
				+ whereZen
				+ SQL_SELECT_URIAGE_SYOSAI_NENKAN_UZ_END
				+ SQL_SELECT_URIAGE_SYOSAI_NENKAN_W
				+ groupBy
				+ orderBy);

		stmt.setString(1, pkGroupCod);
		int seqCnt = 1;
		int tmpCnt = seqCnt;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1 + tmpCnt, (String)whereList.get(i));
			seqCnt++;
		}
		tmpCnt = seqCnt;
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + 1 + tmpCnt, (Integer)whereIntList.get(i));
			seqCnt++;
		}
		stmt.setString(1 + seqCnt, pkGroupCod);
		seqCnt = seqCnt + 1;
		tmpCnt = seqCnt;
		for(int i = 0; i < whereListZen.size(); i++){
			stmt.setString(i + 1 + tmpCnt, (String)whereListZen.get(i));
			seqCnt++;
		}
		tmpCnt = seqCnt;
		for(int i = 0; i < whereIntListZen.size(); i++){
			stmt.setInt(i + 1 + tmpCnt, (Integer)whereIntListZen.get(i));
			seqCnt++;
		}

		ResultSet rs = stmt.executeQuery();

		UriageSyosaiModel uriageSyosaiModel = null;
		while(rs.next()){
			uriageSyosaiModel = new UriageSyosaiModel();
			uriageSyosaiModel.setIntUriageYm(rs.getInt("URIAGE_YM"));
			uriageSyosaiModel.setIntKbn(rs.getInt("KBN"));
			uriageSyosaiModel.setIntSeq(rs.getInt("SEQ"));
			uriageSyosaiModel.setIntAtai(rs.getInt("ATAI"));
			uriageSyosaiModel.setIntAtaiZen(rs.getInt("ATAI_ZEN"));
			resultList.add(uriageSyosaiModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}







	private String SQL_SELECT_URIAGE_NIKKEI =
		" SELECT " +
		"     U.URIAGE_YMD " +
		"    ,SUM(U.SHINKI_SU)  SHINKI_SU" +
		"    ,SUM(U.SAIRAI_SU)  SAIRAI_SU" +
		"    ,SUM(U.SHIMEI_SU)  SHIMEI_SU" +
		"    ,SUM(U.NYUKYAKU_SU)  NYUKYAKU_SU" +
		"    ,SUM(U.HONTAI_KIN)  HONTAI_KIN" +
		"    ,SUM(U.SYOHIZEI_KIN)  SYOHIZEI_KIN" +
		"    ,SUM(U.URIAGE_GOKEI_KIN)  URIAGE_GOKEI_KIN" +
		"    ,SUM(U.TANKA_KIN)  TANKA_KIN" +
		"    ,SUM(U.HOSYU_GOKEI_KIN)  HOSYU_GOKEI_KIN" +
		" FROM " +
		"     T_URIAGE U, " +
		"     M_JUGYOIN J ";

	/**
	 * 売上情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageModel> getUriageNikkeiJoho(Connection con, String pkGroupCod, String pkTenpoCod, String pkJugyoinCod, Integer pkUriageYmd) throws Exception{

		List<UriageModel> resultList = new ArrayList<UriageModel>();

		//検索条件の作成
		String where = " WHERE " +
		"         U.GROUP_COD = J.GROUP_COD " +
		"     AND U.TENPO_COD = J.TENPO_COD " +
		"     AND U.JUGYOIN_SEQ = J.SEQ ";

		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " U.GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " U.TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		if( BSWSUtils.isNotBlankNull(pkJugyoinCod)){
			where += BSWSUtils.getWhereAnd(where) + " J.SEQ = ? ";
			whereList.add(pkJugyoinCod);
		}
		String where2 = "";
		List<Integer> where2List = new ArrayList<Integer>();
		if( pkUriageYmd != null ){
			where2 = BSWSUtils.getWhereAnd(where) + "  ? BETWEEN J.NYUTEN_YMD AND J.TAITEN_YMD ";
			where2List.add(pkUriageYmd);
		}
		if( pkUriageYmd != null ){
			where2 += BSWSUtils.getWhereAnd(where) + " U.URIAGE_YMD = ? ";
			where2List.add(pkUriageYmd);
		}
		String groupBy = " GROUP BY U.URIAGE_YMD ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_NIKKEI + where + where2 + groupBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < where2List.size(); i++){
			stmt.setInt(seqCnt + i + 1, (Integer)where2List.get(i));
		}

		ResultSet rs = stmt.executeQuery();

		UriageModel uriageModel = null;
		while(rs.next()){
			uriageModel = new UriageModel();
			uriageModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageModel.setIntShinkiSu(rs.getInt("SHINKI_SU"));
			uriageModel.setIntSairaiSu(rs.getInt("SAIRAI_SU"));
			uriageModel.setIntShimeiSu(rs.getInt("SHIMEI_SU"));
			uriageModel.setIntNyukyakuSu(rs.getInt("NYUKYAKU_SU"));
			uriageModel.setIntHontaiKin(rs.getInt("HONTAI_KIN"));
			uriageModel.setIntSyohizeiKin(rs.getInt("SYOHIZEI_KIN"));
			uriageModel.setIntUriageGokeiKin(rs.getInt("URIAGE_GOKEI_KIN"));
			uriageModel.setIntTankaKin(rs.getInt("TANKA_KIN"));
			uriageModel.setIntHosyuGokeiKin(rs.getInt("HOSYU_GOKEI_KIN"));
			resultList.add(uriageModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_SELECT_URIAGE_NIKKEI_URIAGE_SHUBETSU =
		" SELECT " +
		"     URIAGE_YMD " +
		"    ,SEQ" +
		"    ,SUM(ATAI)  ATAI" +
		" FROM " +
		"     T_URIAGE_SYOSAI ";

	/**
	 * 売上売上種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageSyosaiModel> getUriageNikkeiSyosaiJoho(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkUriageYmd, Integer pkJugyoinSeq,
			Integer pkKbn, Integer pkSeq
			) throws Exception{

		List<UriageSyosaiModel> resultList = new ArrayList<UriageSyosaiModel>();

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		ArrayList<Integer> whereIntList = new ArrayList<Integer>();
		if( pkUriageYmd != null ){
			where += BSWSUtils.getWhereAnd(where) + " URIAGE_YMD = ? ";
			whereIntList.add(pkUriageYmd);
		}
		if( pkJugyoinSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( pkKbn != null){
			where += BSWSUtils.getWhereAnd(where) + " KBN = ? ";
			whereIntList.add(pkKbn);
		}
		if( pkSeq != null){
			where += BSWSUtils.getWhereAnd(where) + " SEQ = ? ";
			whereIntList.add(pkSeq);
		}

		String groupBy = " GROUP BY URIAGE_YMD, KBN, SEQ ";
		String orderBy = " ORDER BY URIAGE_YMD, KBN, SEQ ";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_NIKKEI_URIAGE_SHUBETSU + where + groupBy + orderBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
		}

		ResultSet rs = stmt.executeQuery();

		UriageSyosaiModel uriageSyosaiModel = null;
		while(rs.next()){
			uriageSyosaiModel = new UriageSyosaiModel();
			uriageSyosaiModel.setIntUriageYmd(rs.getInt("URIAGE_YMD"));
			uriageSyosaiModel.setIntSeq(rs.getInt("SEQ"));
			uriageSyosaiModel.setIntAtai(rs.getInt("ATAI"));
			resultList.add(uriageSyosaiModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_SELECT_URIAGE_GENKIN_SHUTSUNO =
		" SELECT" +
		"     MG.SEQ" +
		"    ,MG.GENKIN_SHUTSUNO_KNJ" +
		"    ,UG.GENKIN_SHUTSUNO_KIN" +
		" FROM" +
		"     T_TENPO_RELATION TR" +
		"     INNER JOIN M_GENKIN_SHUTSUNO MG ON (" +
		"         TR.GROUP_COD = MG.GROUP_COD" +
		"     AND TR.SEQ = MG.SEQ" +
		"     AND TR.RELATION_KBN = " + BSWSConstants.KBN_GENKIN_SHUTSUNO +
		"     )" +
		"     LEFT OUTER JOIN T_URIAGE_GENKIN_SHUTSUNO UG ON (" +
		"         MG.GROUP_COD = UG.GROUP_COD" +
		"     AND TR.TENPO_COD = UG.TENPO_COD" +
		"     AND TR.SEQ = UG.SEQ" +
		"     AND UG.URIAGE_YMD = ? " +
		"     )";

	/**
	 * 現金出納情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<MasterBean> getUriageGenkinShutsunoJoho(Connection con,
			String pkGroupCod, String pkTenpoCod, Integer pkSeq,
			Integer pkUriageYmd) throws Exception{

		List<MasterBean> resultList = new ArrayList<MasterBean>();

		//検索条件の作成
		String where = "";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " TR.GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TR.TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}

		String orderBy = " ORDER BY MG.GENKIN_SHUTSUNO_HYOJI_JUN";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_GENKIN_SHUTSUNO + where + orderBy);
		stmt.setInt(1, pkUriageYmd);
		for(int k = 0; k < whereList.size(); k++){
			stmt.setString(k + 2, (String)whereList.get(k));
		}

		ResultSet rs = stmt.executeQuery();

		MasterBean masterBean = null;
		while(rs.next()){
			masterBean = new MasterBean();
			masterBean.setIntSeq(rs.getInt("SEQ"));
			masterBean.setStrValue(rs.getString("GENKIN_SHUTSUNO_KNJ"));
			masterBean.setIntValue(rs.getInt("GENKIN_SHUTSUNO_KIN"));
			resultList.add(masterBean);
		}

		rs.close();
		stmt.close();

		return resultList;
	}


	private String SQL_DELETE_GENKIN_SHUTSUNO =
		" DELETE FROM " +
		"     T_URIAGE_GENKIN_SHUTSUNO ";

	/**
	 * 現金出納情報削除
	 * 引数のPKがあれば条件とする。
	 */
	public int deleteGenkinShutsunoJoho(Connection con,
			BSWSSession bSWSSession, String pkTenpoCod, Integer pkUriageYmd, Integer pkSeq) throws Exception{

		//削除条件の作成
		String where = "";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(bSWSSession.getStrGroupCod())){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(bSWSSession.getStrGroupCod());
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( pkSeq != null ){
			where += BSWSUtils.getWhereAnd(where) + " SEQ = ? ";
			whereIntList.add(pkSeq);
		}
		if( pkUriageYmd != null ){
			where += BSWSUtils.getWhereAnd(where) + " URIAGE_YMD = ? ";
			whereIntList.add(pkUriageYmd);
		}

		String orderBy = " ";
		PreparedStatement stmt = con.prepareStatement(SQL_DELETE_GENKIN_SHUTSUNO + where + orderBy);

		int seqCnt = 0;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + 1 + seqCnt, (Integer)whereIntList.get(i));
		}

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_INSERT_GENKIN_SHUTSUNO =
		" INSERT INTO " +
		"     T_URIAGE_GENKIN_SHUTSUNO ( " +
		"         GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,URIAGE_YMD " +
		"        ,SEQ " +
		"        ,GENKIN_SHUTSUNO_KIN " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 現金出納情報追加
	 */
	public int insertGenkinShutsuno(Connection con,
			BSWSSession bSWSSession ,
			String strSansyoTenpoCod,
			String strSansyoYmd,
			Integer intSeq,
			Integer intGenkinShutsunoKin) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_INSERT_GENKIN_SHUTSUNO);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setString(2, strSansyoTenpoCod);
		stmt.setInt(3, Integer.parseInt(strSansyoYmd));
		stmt.setInt(4, intSeq);
		stmt.setInt(5, intGenkinShutsunoKin);
		stmt.setString(6, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

}